﻿CREATE PROCEDURE [dbo].[spLoadCustomer]
	@CustomerID int
AS
	declare @Person table ( PersonID int )
	insert into @Person
	select PersonID from tblCustomer 
	where CustomerID = @CustomerID
	union select ShipTo_PersonID from tblOrderHeader 
	where CustomerID = @CustomerID

	declare @Address table ( AddressID int )
	insert into @Address
	select T1.AddressID from tblPerson T1
		join @Person T2 on T1.PersonID = T2.PersonID
	union select T1.ShipTo_AddressID from tblOrderHeader T1
	where T1.CustomerID = @CustomerID

	-- Load tblAddress
	select T1.* from tblAddress T1
		join @Address T2 on T1.AddressID = T2.AddressID

	-- Load tblPerson
	select T1.* from tblPerson T1
		join @Person T2 on T1.PersonID = T2.PersonID

	-- Load the contactPointType
	select distinct T1.* from codeContactPointType T1
		join tblContactPoint T2 on T1.ContactPointTypeCode = T2.ContactPointTypeCode
		join @Person T3 on T2.PersonID = T3.PersonID

	-- Load tblCustomer
	select * from tblCustomer where CustomerID = @CustomerID

	-- Load tblContactPoint
	select distinct T1.* from tblContactPoint T1
		join @Person T2 on T1.PersonID = T2.PersonID

	-- Load codeHoldReason
	select distinct T1.* from codeHoldReason T1
		join tblOrderHeader T2 on T1.HoldReasonCode = T2.HoldReasonCode
	where T2.CustomerID = @CustomerID

	-- Load tblOrderSource
	select distinct T1.* from tblOrderSource T1
		join tblOrderHeader T2 on T1.OrderSourceID = T2.OrderSourceID
	where T2.CustomerID = @CustomerID

	-- Load tblPromotion
	select distinct T1.* from tblPromotion T1
		join tblOrderHeader T2 on T1.PromotionID = T2.PromotionID
	where T2.CustomerID = @CustomerID

	-- Load tblOrderHEader
	select * from tblOrderHeader
	where CustomerID = @CustomerID

	-- Load tblProduct
	select T1.* from tblProduct T1
		join tblOrderDetail T2 on T1.ProductID = T2.ProductID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID  = @CustomerID
	union select T1.* from tblProduct T1
		join tblSequenceProduct T2 on T1.ProductID = T2.ProductID
		join tblSequenceOrder T3 on T2.SequenceID = T3.SequenceID
		join tblOrderHeader T4 on T3.OrderID = T4.OrderID
	where T4.CustomerID  = @CustomerID
	union select T1.* from tblProduct T1
		join tblSequenceOrderStepProduct T2 on T1.ProductID = T2.ProductID
		join tblSequenceOrderStep T3 on T2.SequenceOrderStepID = T3.SequenceOrderStepID
		join tblSequenceOrder T4 on T3.SequenceOrderID = T4.SequenceOrderID
	where T4.CustomerID  = @CustomerID

	-- Load tblOrderDetail
	select T1.* from tblOrderDetail T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblSequence
	select distinct T1.* from tblSequence T1
		join tblSequenceStep T2 on T1.SequenceID = T2.SequenceID
		join tblSequenceOrderStep T3 on T2.SequenceStepID = T3.SequenceStepID
		join tblSequenceOrder T4 on T3.SequenceOrderID = T4.SequenceOrderID
		join tblOrderHeader T5 on T4.OrderID = T5.OrderID
	where T5.CustomerID = @CustomerID

	-- Load tblSequenceOrder
	select distinct T1.* from tblSequenceOrder T1
	where CustomerID = @CustomerID

	-- Load tblSequenceStep
	select T1.* from tblSequenceStep T1
		join tblSequenceOrderStep T2 on T1.SequenceStepID = T2.SequenceStepID
		join tblSequenceOrder T3 on T2.SequenceOrderID = T3.SequenceOrderID
		join tblOrderHeader T4 on T3.OrderID = T4.OrderID
	where T4.CustomerID = @CustomerID

	-- Load tblSequenceStepProduct
	select T1.* from tblSequenceStepProduct T1
		join tblSequenceStep T2 on T1.SequenceStepID = T2.SequenceStepID
		join tblSequenceOrderStep T3 on T2.SequenceStepID = T3.SequenceStepID
		join tblSequenceOrder T4 on T3.SequenceOrderID = T4.SequenceOrderID
		join tblOrderHeader T5 on T4.OrderID = T5.OrderID
	where T5.CustomerID = @CustomerID

	-- Load tblSequenceOrderStep
	select T1.* from tblSequenceOrderStep T1
		join tblSequenceOrder T2 on T1.SequenceOrderID = T2.SequenceOrderID
	where T2.CustomerID = @CustomerID

	-- Load tblSequenceOrderStepProduct
	select T1.* from tblSequenceOrderStepProduct T1
		join tblSequenceOrderStep T2 on T1.SequenceOrderStepID = T2.SequenceOrderStepID
		join tblSequenceOrder T3 on T2.SequenceOrderID = T3.SequenceOrderID
	where T3.CustomerID = @CustomerID

	-- Load tblRefund
	select T1.* from tblRefund T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblRMAReturn
	select T1.* from tblRMAReturn T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblReturnDetail
	select T1.* from tblReturnDetail T1
		join tblRMAReturn T2 on T1.RMAReturnID = T2.RMAReturnID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblTransaction
	select T1.* from tblTransaction T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblTransactionSubmit
	select T1.* from tblTransactionSubmit T1
		join tblTransaction T2 on T1.TransactionID = T2.TransactionID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblAccount
	select T1.* from tblAccount T1
		join tblTransaction T2 on T1.AccountID = T2.AccountID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID
	union select T1.* from tblAccount T1
		join tblOrderHeader T2 on T1.AccountID = T2.AccountID
	where T2.CustomerID = @CustomerID

	-- Load tblTransactionSubmit
	select T1.* from tblTransactionSubmit T1
		join tblTransaction T2 on T1.TransactionID = T2.TransactionID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblMiscDef
	select T1.* from tblMiscDef T1
	where OwnerID = null 
		or OwnerID = ( select OwnerID from tblCustomer where CustomerID = @CustomerID )

	-- Load tblInventory
	select T1.* from tblInventory T1
		join tblInventoryTransaction T2 on T1.InventoryID = T2.InventoryID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblInventoryTransaction
	select T1.* from tblInventoryTransaction T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblInventoryTransactionDetail
	select T1.* from tblInventoryTransactionDetail T1
		join tblInventoryTransaction T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblPackage
	select T1.* from tblPackage T1
		join tblInventoryTransaction T2 on T1.PackageID = T2.PackageID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblAltOrderID
	select T1.* from tblAltOrderID T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblAltCustomerID
	select T1.* from tblAltCustomerID T1
	where T1.CustomerID = @CustomerID

	-- Load tblNote
	select n.* from tblNote n
		join ( select NoteID from tblCustomerNote cn
				where cn.CustomerID = @CustomerID
			   union select NoteID from tblOrderHeader oh
				join tblOrderNote orn on oh.OrderID = orn.OrderID
			   where oh.CustomerID = @CustomerID
			  ) as T2 on n.NoteID = T2.NoteID

	-- Load tblCustomerNote
	select cn.* from tblCustomerNote cn 
	where cn.CustomerID = @CustomerID

	-- Load tblOrderNote
	select orn.* from tblOrderHeader oh
		join tblOrderNote orn on oh.OrderID = orn.OrderID
	where oh.CustomerID = @CustomerID